﻿
using MySql.Data.MySqlClient;
using MySqlX.XDevAPI.Relational;
using System.Configuration;
using System.Data;

// using MySqlConnection connection = new("server=127.0.0.1;uid=root;pwd=root;database=demodb");

// 打开连接 默认是关闭的
//connection.Open();
// 创建命令
//MySqlCommand command = new("insert into account(name,money) values('王五',5000)",connection);
// 执行增删改命令语句
//command.ExecuteNonQuery();

// 创建命令
//MySqlCommand command = new("select count(*) from account", connection);
//var obj = Convert.ToInt32(command.ExecuteScalar());


// 读取数据
//using MySqlCommand command = new("select * from account",connection);
//using MySqlDataReader reader = command.ExecuteReader();
// 行流，读取数据

/*while (reader.Read())
{
    // 第一种读取方式
    var idIndex = reader.GetOrdinal("id");
    var idValue = reader.GetValue(idIndex);

    var nameIndex = reader.GetOrdinal("name");
    var nameValue = reader.GetValue(nameIndex);

    var moneyIndex = reader.GetOrdinal("money");
    var moneyValue = reader.GetValue(moneyIndex);

    Console.WriteLine($"id:{idValue},name:{nameValue},money:{moneyValue}");
}*/
/*
while (reader.Read())
{
    // 第二种读取方式
    var account = new Account(
        reader.GetInt32(0),
        reader.GetString(1),
        reader.GetDecimal(2)
    );

    //Console.WriteLine($"id:{account.Id},name:{account.name},money:{account.money}");

    // 第三种读取方式
    var account2 = new Account(
        Convert.ToInt32(reader["id"]),
        reader["name"].ToString(),
        Convert.ToDecimal(reader["money"])
    );
    Console.WriteLine($"id:{account2.Id},name:{account2.name},money:{account2.money}");
}*/

/*
 * record 是C#9.0的语法，用于创建不可变对象，提供了一种简介的方式来定义只读数据结构
 * 他还提供了一些内助的特性，比如自动生成的Equals，GetHashCode和ToString方法，
 */
//record Account(int Id, string name, decimal money);

// 创建适配器 此处我并未手动的打开连接
//MySqlDataAdapter adapter = new();
// 创建命令对象
//adapter.SelectCommand = new("select * from account", connection);

// 读取数据(内存中的容器)
//DataSet dataSet = new DataSet();
//adapter.Fill(dataSet);
//Console.WriteLine(dataSet.Tables[0].Rows.Count);

/*
    1.List 更符合面向对象编程思想
    2.row 在操作列名的时候，有可能会将列名写错
    3.List 集合操作速度要比DataTable要快，并且数据类型安全
 */
/*
List<Account> accountlist = new List<Account>();
MySqlDataAdapter adapter = new MySqlDataAdapter("select * from account",connection);
DataTable dataTable = new DataTable();
adapter.Fill(dataTable);
foreach (DataRow row in dataTable.Rows)
{
    // DBNull 与 C#中的null是不一样
    Account account = new Account();
    account.id = Convert.ToInt32(row["Id"]);
    account.name = row["name"].ToString();
    if (row["money"] is not DBNull)
    {
        account.money = Convert.ToDecimal(row["money"]);
    }
    accountlist.Add(account);
}

foreach (Account account in accountlist)
{
    Console.WriteLine($"id:{account.id},name:{account.name},money:{account.money}");
}

 class Account
{
    public int id { get; set; }

    public string name { get; set; }

    public decimal money { get; set; }

}
*/

// 读取配置文件
/*string connString = ConfigurationManager.ConnectionStrings["localString"].ConnectionString;
List<Account> accountList = new List<Account>();
using MySqlConnection connection = new MySqlConnection(connString);

MySqlCommand command = new MySqlCommand("select * from account", connection);
using MySqlDataAdapter adapter = new MySqlDataAdapter(command);
DataTable dataTable = new DataTable();
adapter.Fill(dataTable);

foreach (DataRow row in dataTable.Rows)
{
    Account account = new Account();
    account.id = Convert.ToInt32(row["id"]);
    account.name = row["name"].ToString();
    if (row["money"] is not DBNull)
    {
        account.money = Convert.ToDecimal(row["money"]);
    }
}


class Account
{
    public int id { get; set; }

    public string name { get; set; }

    public decimal money { get; set; }
}**/

var connectionString = ConfigurationManager.ConnectionStrings["localString"].ConnectionString;
MySqlDataAdapter adapter = new MySqlDataAdapter("select * from account where name = @name and money =@money", connectionString);

MySqlParameter[] parameters =
{
    new("name",SqlDbType.VarChar),
    new("money",SqlDbType.Float)
};

parameters[0].Value = "张三";
parameters[1].Value = "3000";
adapter.SelectCommand.Parameters.AddRange(parameters);


DataTable datatable = new DataTable();
adapter.Fill(datatable);



